'Imports Microsoft.VisualBasic
    Imports System.Data.OracleClient
    Imports System.Data
    Imports System.Diagnostics

    Imports log4net
    Imports log4net.Config

    Public Class DBTool
        Private connstr As String = ""
        Private showSQL As Boolean = False
        Private mExecutionTime As TimeSpan

        Public Property ExecutionTime As TimeSpan
            Get
                Return mExecutionTime
            End Get
            Private Set(ByVal value As TimeSpan)
                mExecutionTime = value
            End Set
        End Property

        Public Sub New(ByVal connectionString As String)
            connstr = connectionString
        End Sub
        Public Sub New(ByVal show As Boolean)
            showSQL = show
            connstr = Labs.getConnStr()
        End Sub
        Public Sub New()
            connstr = Labs.getConnStr()
        End Sub
        Private Function getDataTable(ByVal SQLString As String, ByVal SQLParameter As Dictionary(Of String, Object), ByVal arr As List(Of Dictionary(Of String, Object)), ByVal type As Integer) As DataTable
            Dim sw As New Stopwatch()

            Dim dtable As New DataTable()
            Dim sqllist As New List(Of String)
            Try
                Using conn As New OracleConnection(connstr)
                    conn.Open()

                    If type = 1 Then
                        Using da As New OracleDataAdapter(SQLString, conn)
                            sw.Start()
                            If Not SQLParameter Is Nothing Then
                                For Each tempParameter As KeyValuePair(Of String, Object) In SQLParameter
                                    da.SelectCommand.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value)
                                Next tempParameter
                            End If
                            sqllist.Add(getRealSql(da.SelectCommand))
                            da.Fill(dtable)

                            sw.Stop()
                            ExecutionTime = sw.Elapsed
                            If ExecutionTime.TotalSeconds > 3.0R Then
                                LogManager.GetLogger("SqlOverTime").Warn(ExecutionTime.ToString + "秒 " + getRealSql(da.SelectCommand))
                            End If
                        End Using
                    Else
                        Using cmd As New OracleCommand(SQLString, conn)
                            If Not arr Is Nothing Then
                                sw.Start()
                                Dim trn = cmd.Connection.BeginTransaction()
                                cmd.Transaction = trn
                                Try
                                    For Each dic As Dictionary(Of String, Object) In arr
                                        For Each tempParameter As KeyValuePair(Of String, Object) In dic
                                            cmd.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value)
                                        Next tempParameter
                                        sqllist.Add(getRealSql(cmd))
                                        cmd.ExecuteNonQuery()
                                    Next dic
                                    trn.Commit()

                                Catch ex As Exception
                                    trn.Rollback()
                                    Throw ex
                                End Try
                                sw.Stop()
                                ExecutionTime = sw.Elapsed
                                If ExecutionTime.TotalSeconds > 3.0R Then
                                    LogManager.GetLogger("SqlOverTime").Warn(ExecutionTime.ToString + "秒 " + getRealSql(cmd))
                                End If
                            ElseIf Not SQLParameter Is Nothing Then
                                sw.Start()
                                For Each tempParameter As KeyValuePair(Of String, Object) In SQLParameter
                                    cmd.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value)
                                Next tempParameter
                                sqllist.Add(getRealSql(cmd))
                                cmd.ExecuteNonQuery()
                                sw.Stop()
                                ExecutionTime = sw.Elapsed
                                If ExecutionTime.TotalSeconds > 3.0R Then
                                    LogManager.GetLogger("SqlOverTime").Warn(ExecutionTime.ToString + "秒 " + getRealSql(cmd))
                                End If
                            End If
                        End Using
                    End If
                    conn.Close()
                End Using
                If showSQL Then
                    For Each tmp_sql In sqllist

                        Labs.saveLog("show Sql：" + tmp_sql)

                    Next
                End If

            Catch ex As Exception

                For Each tmp_sql In sqllist

                    Labs.saveLog("Error Sql：" + tmp_sql)

                Next
                Throw ex
            End Try
            Return dtable
        End Function
        '
        '查詢數量(查詢語法)
        '
        <Obsolete("這個Method已被DataHelper.Count取代")>
        Function queryCount(ByVal SQLString As String) As Integer

            Return getDataTable(SQLString, Nothing, Nothing, 1).Rows(0)(0)

        End Function
        '
        '查詢資料庫(查詢語法)
        '
        <Obsolete("這個Method已被DataHelper.DataTable取代")>
        Function queryData(ByVal SQLString As String) As DataTable

            Return getDataTable(SQLString, Nothing, Nothing, 1)

        End Function
        '
        '查詢資料庫(查詢語法,參數集合)
        '
        <Obsolete("這個Method已被DataHelper.Count取代")>
        Function queryCount(ByVal SQLString As String, ByVal SQLParameter As Dictionary(Of String, Object)) As Integer

            Return getDataTable(SQLString, SQLParameter, Nothing, 1).Rows(0)(0)

        End Function
        '
        '查詢資料庫(查詢語法,參數集合)
        '
        <Obsolete("這個Method已被DataHelper.DataTable取代")>
        Function queryData(ByVal SQLString As String, ByVal SQLParameter As Dictionary(Of String, Object)) As DataTable

            Return getDataTable(SQLString, SQLParameter, Nothing, 1)

        End Function
        '
        '查詢資料庫(查詢語法,單一值)
        '
        <Obsolete("這個Method已被DataHelper.DataScalar取代")>
        Function queryScalar(ByVal SQLString As String) As String
            Try
                Using conn As New OracleConnection(connstr)
                    conn.Open()
                    Using da As New OracleDataAdapter(SQLString, conn)
                        Dim dtable As New DataTable()
                        da.Fill(dtable)
                        If dtable.Rows.Count = 0 Then
                            Return String.Empty
                        Else
                            Return dtable.Rows(0)(0).ToString
                        End If

                    End Using
                End Using
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        '
        '查詢資料庫(查詢語法,參數集合,單一值)
        '
        <Obsolete("這個Method已被DataHelper.DataScalar取代")>
        Function queryScalar(ByVal SQLString As String, ByVal SQLParameter As Dictionary(Of String, Object)) As String
            Try
                Using conn As New OracleConnection(connstr)
                    conn.Open()
                    Using da As New OracleDataAdapter(SQLString, conn)
                        Dim dtable As New DataTable()
                        For Each tempParameter As KeyValuePair(Of String, Object) In SQLParameter
                            da.SelectCommand.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value)
                        Next tempParameter
                        da.Fill(dtable)
                        If dtable.Rows.Count = 0 Then
                            Return ""
                        Else
                            Return dtable.Rows(0)(0).ToString
                        End If
                        'If (Not IsDBNull(dtable.Rows(0)(0))) Then
                        '    Return dtable.Rows(0)(0).ToString
                        'Else
                        '    Return String.Empty
                        'End If
                    End Using
                End Using
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        '
        '操作資料庫(查詢語法,返回第一行第一列)
        '
        Public Function ExecuteScalar(sql As String) As String
            Try
                Using conn As New OracleConnection(connstr)
                    conn.Open()
                    Using cmd As New OracleCommand(sql, conn)
                        Return cmd.ExecuteScalar() & ""
                    End Using

                    'return new SqlCommand(sql, connection).ExecuteScalar() + "";
                End Using
                'closeConnection();
            Finally
            End Try
        End Function

        '
        '操作資料庫(查詢語法,參數集合,返回第一行第一列)
        '
        <Obsolete("這個Method已被DataHelper.DataScalar取代")>
        Public Function ExecuteScalar(cmdType As CommandType, cmdText As String, ParamArray cmdParms As OracleParameter()) As Object
            Dim cmd As New OracleCommand()

            Using conn As New OracleConnection(connstr)
                PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, cmdParms)
                Dim val As Object = cmd.ExecuteScalar()
                cmd.Parameters.Clear()
                Return val
            End Using
        End Function

        '
        '操作資料庫(查詢語法,參數集合,返回DataReader)
        '
        <Obsolete("這個Method已被DataHelper.DataReader和DataHelper.GetDataReader()取代")>
        Public Function ExecuteReader(cmdType As CommandType, cmdText As String, ParamArray cmdParms As OracleParameter()) As OracleDataReader
            Dim cmd As New OracleCommand()
            Dim conn As New OracleConnection(connstr)

            Try
                PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, cmdParms)
                Dim rdr As OracleDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                cmd.Parameters.Clear()
                Return rdr
            Catch
                conn.Close()
                Throw
            End Try
        End Function

        '
        '準備命令作業
        '
        <Obsolete("這個Method已被DataHelper.SqlCommand取代")>
        Private Shared Sub PrepareCommand(cmd As OracleCommand, conn As OracleConnection, trans As OracleTransaction, cmdType As CommandType, cmdText As String, cmdParms As OracleParameter())

            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            cmd.Connection = conn
            cmd.CommandText = cmdText

            If trans IsNot Nothing Then
                cmd.Transaction = trans
            End If

            cmd.CommandType = cmdType

            If cmdParms IsNot Nothing Then
                For Each parm As OracleParameter In cmdParms
                    cmd.Parameters.Add(parm)
                Next
            End If
        End Sub


        '
        '操作資料庫(操作語法,參數集合)
        '
        <Obsolete("這個Method已被DataHelper.ExecuteNonQuery()取代")>
        Sub executeNonQuery(ByVal SQLString As String, ByVal SQLParameter As Dictionary(Of String, Object))
            Try
                getDataTable(SQLString, SQLParameter, Nothing, 2)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        '
        '批次操作資料庫(操作語法,參數集合)
        '
        <Obsolete("這個Method已被DataHelper.ExecuteNonQuery()取代")>
        Sub executeNonQuery(ByVal SQLString As String, ByVal arr As List(Of Dictionary(Of String, Object)))
            Try
                getDataTable(SQLString, Nothing, arr, 2)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        '設定ddl
        Public Sub setDDL(ByRef ddlobj As DropDownList, ByVal sql As String, ByVal SQLParameter As Dictionary(Of String, Object), ByVal textcol As String, ByVal valcol As String)
            ddlobj.DataSource = queryData(sql, SQLParameter)
            ddlobj.DataTextField = textcol
            ddlobj.DataValueField = valcol
            ddlobj.DataBind()
        End Sub
        '設定ddl
        Public Sub setDDL(ByRef ddlobj As DropDownList, ByVal sql As String, ByVal textcol As String, ByVal valcol As String)
            ddlobj.DataSource = queryData(sql)
            ddlobj.DataTextField = textcol
            ddlobj.DataValueField = valcol
            ddlobj.DataBind()
        End Sub
        '設定分數ddl
        Public Sub setDDL(ByRef ddlobj As DropDownList, ByVal Score As Dictionary(Of String, String), ByVal textcol As String, ByVal valcol As String)
            ddlobj.DataSource = Score
            ddlobj.DataTextField = textcol
            ddlobj.DataValueField = valcol
            ddlobj.DataBind()
        End Sub
        '設定lbx
        Public Sub setLBX(ByRef lbxobj As ListBox, ByVal sql As String, ByVal SQLParameter As Dictionary(Of String, Object), ByVal textcol As String, ByVal valcol As String)
            lbxobj.DataSource = queryData(sql, SQLParameter)
            lbxobj.DataTextField = textcol
            lbxobj.DataValueField = valcol
            lbxobj.DataBind()
        End Sub
        '設定lbx
        Public Sub setLBX(ByRef lbxobj As ListBox, ByVal sql As String, ByVal textcol As String, ByVal valcol As String)
            lbxobj.DataSource = queryData(sql)
            lbxobj.DataTextField = textcol
            lbxobj.DataValueField = valcol
            lbxobj.DataBind()
        End Sub
        '設定rdblist
        Public Sub setRDBList(ByRef rdblistobj As RadioButtonList, ByVal sql As String, ByVal SQLParameter As Dictionary(Of String, Object), ByVal textcol As String, ByVal valcol As String)
            rdblistobj.DataSource = queryData(sql, SQLParameter)
            rdblistobj.DataTextField = textcol
            rdblistobj.DataValueField = valcol
            rdblistobj.DataBind()
        End Sub
        '設定rdblist
        Public Sub setRDBList(ByRef rdblistobj As RadioButtonList, ByVal sql As String, ByVal textcol As String, ByVal valcol As String)
            rdblistobj.DataSource = queryData(sql)
            rdblistobj.DataTextField = textcol
            rdblistobj.DataValueField = valcol
            rdblistobj.DataBind()
        End Sub
        '設定cbxlist
        Public Sub setCBXList(ByRef cbxlistobj As CheckBoxList, ByVal sql As String, ByVal SQLParameter As Dictionary(Of String, Object), ByVal textcol As String, ByVal valcol As String)
            cbxlistobj.DataSource = queryData(sql, SQLParameter)
            cbxlistobj.DataTextField = textcol
            cbxlistobj.DataValueField = valcol
            cbxlistobj.DataBind()
        End Sub
        '設定cbxlist
        Public Sub setCBXList(ByRef cbxlistobj As CheckBoxList, ByVal sql As String, ByVal textcol As String, ByVal valcol As String)
            cbxlistobj.DataSource = queryData(sql)
            cbxlistobj.DataTextField = textcol
            cbxlistobj.DataValueField = valcol
            cbxlistobj.DataBind()
        End Sub
        '設定頁面權限
        Public Sub setPermissions(ByRef vo As PermissionsVo, ByRef req As HttpRequest, ByVal type As Integer)
            Dim sql = " SELECT * FROM FXTMENU M WHERE MENUSNO=:MENUSNO AND UGROUPSNO = :UGROUPSNO "
            Dim psdic As New Dictionary(Of String, Object)
            Dim MENUSNO = ""
            If (type = 1) Then '查詢頁面
                Dim menosno = 0
                Integer.TryParse(req.QueryString("a"), menosno)
                UserSession.MENUSNO = menosno
            ElseIf (type = 2) Then
                '來源頁面
                Dim x = req.ServerVariables
                UserSession.PRE_PAGE = req.UrlReferrer.ToString()
            ElseIf (type = 3) Then
                '來源頁面
                'UserSession.PRE_PAGE = req.UrlReferrer.ToString()
            End If
            MENUSNO = UserSession.MENUSNO
            psdic.Add("MENUSNO", MENUSNO)
            psdic.Add("UGROUPSNO", UserSession.UGROUPSNO)
            Dim dtable As DataTable = queryData(sql, psdic)
            If (dtable.Rows.Count > 0) Then
                vo.ISDELETE = dtable(0)("ISDELETE")
                vo.ISINSERT = dtable(0)("ISINSERT")
                vo.ISQUERY = dtable(0)("ISQUERY")
                vo.ISUPDATE = dtable(0)("ISUPDATE")
                vo.ISVIEW = dtable(0)("ISVIEW")
                vo.MEMBNO = MENUSNO
            End If
        End Sub

        Sub setDDL(p1 As String, ddlsql As String, p3 As String, p4 As String)
            Throw New NotImplementedException
        End Sub


        '
        '紀錄LOG
        '
        ''' <summary>
        ''' 此紀錄請在做完動作的最後呼叫
        ''' </summary>
        ''' <param name="MENUSNO">前台填寫-2,後台登入和登出填寫-1，其他功能 從  PermissionsVo 可以取出 membno  ex: vo.membno</param>
        ''' <param name="ISFAIL">失敗填1，成功填0</param>
        ''' <param name="ACCSCD">'A'=>新增；'U'=>修改；'D'=>刪除；'Q'=>查詢；'L'=>登入；'O'=>登出；</param>
        ''' <param name="ACCSDESC">註記，沒有需要註記的話傳空字串""進來</param>
        ''' <remarks></remarks>
        Sub accessLog(ByVal MENUSNO As String, ByVal ISFAIL As Integer, ByVal ACCSCD As String, ByVal ACCSDESC As String _
                      , Optional UserId As String = "", Optional nPage As Page = Nothing)
            Try
                Dim dic As New Dictionary(Of String, Object)()
                Dim sql As String = "INSERT INTO FXTACCSLOG (MENUSNO,USERSNO,UGROUPSNO,IP,ISFAIL,ACCSCD,ACCSDESC) " +
                                    "VALUES(:MENUSNO,:USERSNO,:UGROUPSNO,:IP,:ISFAIL,:ACCSCD,:ACCSDESC )"
                Dim ip As String
                If Not nPage Is Nothing Then ip = nPage.Request.ServerVariables("REMOTE_ADDR").ToString
                dic.Add(":MENUSNO", MENUSNO)
                If MENUSNO = "-2" Then  '前台
                    dic.Add(":USERSNO", UserId)
                    dic.Add(":UGROUPSNO", -1)
                    dic.Add(":IP", nPage.Request.ServerVariables("REMOTE_ADDR").ToString)
                Else
                    dic.Add(":USERSNO", UserSession.USERSNO)
                    dic.Add(":UGROUPSNO", UserSession.UGROUPSNO)
                    dic.Add(":IP", UserSession.IP)
                End If
                dic.Add(":ISFAIL", ISFAIL)
                dic.Add(":ACCSCD", ACCSCD)
                dic.Add(":ACCSDESC", ACCSDESC)
                executeNonQuery(sql, dic)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        '
        'MAILLOG
        '
        ''' <summary>
        ''' 此紀錄請在發完MAIL後呼叫
        ''' </summary>
        ''' <param name="SENDTOMAIL">所寄送的MAIL</param>
        ''' <param name="MAILTITLE">MAIL標題</param>
        ''' <param name="CONTENT">MAIL內容</param>
        ''' <param name="ISFAIL">失敗填1，成功填0</param>
        ''' <param name="MEMO">MEMO</param>
        ''' <remarks></remarks>
        Function accessMailLog(ByVal SENDTOMAIL As String, ByVal MAILTITLE As String, ByVal CONTENT As String, ByVal ISFAIL As Integer, ByVal MEMO As String) As Boolean
            Try
                Dim dic As New Dictionary(Of String, Object)()
                Dim sql As String = "INSERT INTO FXTMAILLOG (MAILLOGSNO,SENDTOMAIL,MAILTITLE,CONTENT,ISFAIL,MEMO) " +
                                    "VALUES(FXTMAILLOG_SEQ.NEXTVAL,:SENDTOMAIL,:MAILTITLE,:CONTENT,:ISFAIL,:MEMO )"
                dic.Add(":SENDTOMAIL", SENDTOMAIL)
                dic.Add(":MAILTITLE", MAILTITLE)
                dic.Add(":CONTENT", CONTENT)
                dic.Add(":ISFAIL", ISFAIL)
                dic.Add(":MEMO", MEMO)

                executeNonQuery(sql, dic)
            Catch ex As Exception
                Throw ex
            End Try
            Return True
        End Function
        Function getRealSql(ByVal ocmd As OracleCommand) As String
            Dim sql = ocmd.CommandText

            For i = 0 To ocmd.Parameters.Count - 1
                Dim tmp_op = ocmd.Parameters(i)
                Dim tmp_p = ":" + tmp_op.ParameterName.ToString().Replace(":", "")

                Dim name As String = If(
                        tmp_op.ParameterName.StartsWith(":"),
                        tmp_op.ParameterName,
                        ":" & tmp_op.ParameterName
                    )

                Dim value As String = If(
                        TypeOf tmp_op.Value Is Date,
                        String.Format(
                            "TO_DATE('{0}', '{1}')",
                            CType(tmp_op.Value, Date).ToString("yyyy/MM/dd HH:mm:ss"), "yyyy/mm/dd hh24:mi:ss"
                        ),
                        String.Format("'{0}'", tmp_op.Value)
                    )
                Dim pattern As String = String.Format("{0}(?=[\W])|{0}$", name)
                sql = Regex.Replace(sql, pattern, value, RegexOptions.IgnoreCase)
                'sql = sql.Replace(tmp_p, getParameterValForOracle(tmp_op))
            Next
            Return sql
        End Function
        Private Function getParameterValForOracle(ByVal op As OracleParameter) As String
            Dim retval = ""
            If IsDBNull(op.Value) Then
                Return " NULL "
            End If
            Select Case op.DbType
                Case DbType.Date
                    retval = "TO_DATE('" + CType(op.Value, Date).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy/mm/dd hh24:mi:ss') "
                Case DbType.DateTime
                    retval = "TO_DATE('" + CType(op.Value, DateTime).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy/mm/dd hh24:mi:ss') "
                Case DbType.DateTime2
                    retval = "TO_DATE('" + CType(op.Value, DateTime).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy/mm/dd hh24:mi:ss') "
                Case Else
                    retval = "'" + op.Value.ToString() + "'"
            End Select
            Return retval
        End Function
    End Class

    '==========================================================f=
    '使用方法範例

    Public Sub InsertPackage(ByVal TSSNO As String, ByVal oTSGROUP As String, ByVal PERSONSNO As String)
        Dim dbtool As New DBTool
        Dim dic As New Dictionary(Of String, Object)
        Dim list_dic As New List(Of Dictionary(Of String, Object))
        Dim sql As String = ""

        '===========↓執行教師複訓Package開課作業===========
        Dim strFXTTSTAT_sd As String = ""

        '新群組
        dic.Clear()
        dic.Add(":TSSNO", TSSNO)
        Dim nTSGROUP = dbtool.queryScalar("SELECT MAX(TSGROUP)+1 FROM FXTTS ")

        dic.Clear()
        dic.Add(":TSGROUP", oTSGROUP)
        Dim CYCLE As String = dbtool.queryScalar(" SELECT CYCLE FROM FXTTRAINING WHERE TSGROUP = :TSGROUP ", dic)
        Dim addMonth As Integer = CInt(CYCLE)     '月份

        Dim sDateT As Date = DateAdd("m", addMonth, DateTime.Now.ToString("yyyy-MM-dd"))    '只取日期 = 現在系統日期 + 教師複訓週期月份
        Dim TrainingSD = String.Format("{0:yyyy-MM-dd}", sDateT)

        '該(學員 + Package)的TSSNO
        sql = "SELECT TSSNO FROM FXTTSS WHERE TSGROUP = :TSGROUP AND PERSONSNO = :PERSONSNO ORDER BY TSSNO "
        dic.Clear()
        dic.Add(":TSGROUP", oTSGROUP)
        dic.Add(":PERSONSNO", PERSONSNO)
        Dim aoTSsno As DataTable = dbtool.queryData(sql, dic)

        '======================日期
        Dim dic_aoTs_Date As New Dictionary(Of String, Object)

        dic_aoTs_Date.Add(":TSSNO", aoTSsno.Rows(0)(0).ToString)

        Dim SDATE As String = dbtool.queryScalar(" SELECT TO_CHAR(SDATETIME,'YYYY-MM-DD') FROM FXTTS WHERE TSSNO = :TSSNO ", dic_aoTs_Date)
        Dim EDATE As String = dbtool.queryScalar(" SELECT TO_CHAR(EDATETIME,'YYYY-MM-DD') FROM FXTTS WHERE TSSNO = :TSSNO ", dic_aoTs_Date)

        Dim sDateSum = DateDiff("D", SDATE, TrainingSD)
        Dim eDateSum = DateDiff("D", EDATE, TrainingSD)

        '======================

        'FXTTS

        For i = 0 To aoTSsno.Rows.Count - 1
            '======================日期

            dic_aoTs_Date.Clear()
            dic_aoTs_Date.Add(":TSSNO", aoTSsno.Rows(i)(0).ToString)
            Dim aoTs_Date As DataTable = dbtool.queryData(" SELECT TO_CHAR(SDATETIME,'YYYY-MM-DD'),TO_CHAR(EDATETIME,'YYYY-MM-DD') FROM FXTTS WHERE TSSNO = :TSSNO ", dic_aoTs_Date)

            Dim addSdate As Date = DateAdd("d", sDateSum, aoTs_Date.Rows(0)(0).ToString)
            Dim addEdate As Date = DateAdd("d", eDateSum, aoTs_Date.Rows(0)(1).ToString)

            Dim Ts_time As DataTable = dbtool.queryData("SELECT TO_CHAR(SDATETIME,'HH24:MI:SS'),TO_CHAR(EDATETIME,'HH24:MI:SS') FROM FXTTS WHERE TSSNO = :TSSNO ", dic_aoTs_Date)
            Dim sDateTime As String = String.Format("{0:yyyy-MM-dd}", addSdate)
            Dim eDateTime As String = String.Format("{0:yyyy-MM-dd}", addEdate)

            sDateTime = String.Format(sDateTime + " " + Ts_time.Rows(0)(0).ToString)
            eDateTime = String.Format(eDateTime + " " + Ts_time.Rows(0)(1).ToString)

            Dim str As String = aoTSsno.Rows(i)(0).ToString()
            If str = TSSNO Then
                strFXTTSTAT_sd = sDateTime
            End If
            '======================

            Dim tmp_dic As New Dictionary(Of String, Object)
            tmp_dic.Add(":TSSNO", aoTSsno.Rows(i)(0).ToString)
            tmp_dic.Add(":TSGROUP", nTSGROUP)
            tmp_dic.Add(":SDATETIME", sDateTime)
            tmp_dic.Add(":EDATETIME", eDateTime)
            tmp_dic.Add(":VENUESNO", "")
            tmp_dic.Add(":TEACHERSNO", "")
            list_dic.Add(tmp_dic)
        Next
        'Return
        sql = "INSERT INTO FXTTS (TSSNO,TSGROUP,COURSEVSNO,SUBJECTVSNO,SDATETIME,EDATETIME,VENUESNO," +
            "TEACHERSNO,TYPE,SUBJECTGROUP,ISEXTRA,USERSNO,TSORDER,VBOOKING,FROMSUBJECTVSNO,COURSEVNUM,TBOOKING) " +
            " SELECT FXTTS_SEQ.NEXTVAL,:TSGROUP,COURSEVSNO,SUBJECTVSNO,TO_DATE(:SDATETIME,'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:EDATETIME,'YYYY-MM-DD HH24:MI:SS'),:VENUESNO," +
            ":TEACHERSNO,TYPE,SUBJECTGROUP,ISEXTRA,USERSNO,TSORDER,VBOOKING,FROMSUBJECTVSNO,COURSEVNUM,TBOOKING " +
            "FROM FXTTS WHERE TSSNO = :TSSNO "
        dbtool.executeNonQuery(sql, list_dic)

        'FXTTSFR
        Dim tsfr_sql = " INSERT INTO FXTTSFR (TSFRSNO,TSGROUP,SUBJECTVSNO,TR,FR,ER,QR) SELECT FXTTSFR_SEQ.NEXTVAL,:nTSGROUP,T.* FROM( SELECT SUBJECTVSNO " +
",NVL( (SELECT SFR.SFRSNO FROM FXTSFR SFR WHERE SFR.SELECTED =1 AND SFR.SUBJECTVSNO=TS.SUBJECTVSNO AND SFR.PAPERTYPE IN ('T1','T2') AND ROWNUM=1 )  ,-1) AS TR " +
",NVL( (SELECT SFR.SFRSNO FROM FXTSFR SFR WHERE SFR.SELECTED =1 AND SFR.SUBJECTVSNO=TS.SUBJECTVSNO AND SFR.PAPERTYPE IN ('F') AND ROWNUM=1)  ,-1) AS FR " +
",NVL( (SELECT SFR.SFRSNO FROM FXTSFR SFR WHERE SFR.SELECTED =1 AND SFR.SUBJECTVSNO=TS.SUBJECTVSNO AND SFR.PAPERTYPE IN ('E') AND ROWNUM=1)  ,-1) AS ER " +
",NVL( (SELECT SFR.SFRSNO FROM FXTSFR SFR WHERE SFR.SELECTED =1 AND SFR.SUBJECTVSNO=TS.SUBJECTVSNO AND SFR.PAPERTYPE IN ('Q') AND ROWNUM=1)  ,-1) AS QR FROM FXTTS TS " +
" WHERE TSGROUP=:oTSGROUP  GROUP BY TSGROUP,SUBJECTVSNO)T "
        Dim tsfr_dic As New Dictionary(Of String, Object)
        tsfr_dic.Add(":nTSGROUP", nTSGROUP)
        tsfr_dic.Add(":oTSGROUP", oTSGROUP)
        dbtool.executeNonQuery(tsfr_sql, tsfr_dic)

        'FXTTRAINING 
        sql = "INSERT INTO FXTTRAINING (TRAININGSNO,TRAININGNAME,TRAININGCOST,TTYPESNO,TSGROUP,USERSNO,TEACHERCOST,VENUECOST,ISVIEWCOST,ISPAY) " +
            "SELECT FXTTRAINING_SEQ.NEXTVAL,TRAININGNAME,TRAININGCOST,TTYPESNO,:nTSGROUP,USERSNO,TEACHERCOST,VENUECOST,ISVIEWCOST,ISPAY " +
            " FROM FXTTRAINING WHERE TSGROUP = :oTSGROUP "
        dic.Clear()
        dic.Add(":nTSGROUP", nTSGROUP)
        dic.Add(":oTSGROUP", oTSGROUP)
        dbtool.executeNonQuery(sql, dic)

        'FXTTSS
        sql = "SELECT TSSNO FROM FXTTS WHERE TSGROUP = :nTSGROUP ORDER BY TSSNO "
        dic.Clear()
        dic.Add(":nTSGROUP", nTSGROUP)
        Dim anTSsno As DataTable = dbtool.queryData(sql, dic)

        sql = " SELECT TSSSNO FROM FXTTSS WHERE TSGROUP = :oTSGROUP AND PERSONSNO = :PERSONSNO ORDER BY TSSSNO "
        dic.Clear()
        dic.Add(":oTSGROUP", oTSGROUP)
        dic.Add(":PERSONSNO", PERSONSNO)
        Dim aoTSSsno As DataTable = dbtool.queryData(sql, dic)

        list_dic.Clear()
        For i = 0 To aoTSSsno.Rows.Count - 1
            Dim tmp_dic As New Dictionary(Of String, Object)
            tmp_dic.Add(":anTSsno", anTSsno.Rows(i)(0).ToString)
            tmp_dic.Add(":aoTSSsno", aoTSSsno.Rows(i)(0).ToString)
            tmp_dic.Add(":nTSGROUP", nTSGROUP)
            tmp_dic.Add(":PERSONSNO", PERSONSNO)
            list_dic.Add(tmp_dic)
        Next
        sql = "INSERT INTO FXTTSS (TSSSNO,TSSNO,TSGROUP,COURSEVSNO,SUBJECTVSNO,SUBJECTGROUP,PERSONSNO,TEACHERSNO," +
            "FROMSUBJECTVSNO,TSORDER,IOEDATE,ROLLCALL,ROLLCALLTIME) " +
            " SELECT FXTTSS_SEQ.NEXTVAL,:anTSsno,:nTSGROUP,COURSEVSNO,SUBJECTVSNO,SUBJECTGROUP,:PERSONSNO,TEACHERSNO," +
            "FROMSUBJECTVSNO,TSORDER,IOEDATE,ROLLCALL,ROLLCALLTIME " +
            "FROM FXTTSS WHERE TSSSNO = :aoTSSsno "
        dbtool.executeNonQuery(sql, list_dic)

        '訓練統計 
        dic.Clear()
        dic.Add(":TSSNO", TSSNO)
        Dim strCOURSESNO As String = dbtool.queryScalar("SELECT COURSESNO FROM FXTCOURSEV WHERE COURSEVSNO = ( SELECT COURSEVSNO FROM FXTTS WHERE TSSNO = :TSSNO )", dic)

        dic.Clear()
        dic.Add(":TSGROUP", nTSGROUP)
        dic.Add(":PERSONSNO", PERSONSNO)
        dic.Add(":SDATETIME", strFXTTSTAT_sd)
        dic.Add(":COURSESNO", strCOURSESNO)
        sql = " INSERT INTO FXTTSTAT (TSTATSNO,TSGROUP,PERSONSNO,SDATETIME,COURSESNO) " +
            " VALUES(FXTTSTAT_SEQ.NEXTVAL,:TSGROUP,:PERSONSNO,TO_DATE(:SDATETIME,'YYYY-MM-DD HH24:MI:SS'),:COURSESNO) "
        dbtool.executeNonQuery(sql, dic)
        '===========↑執行教師複訓Package開課作業===========



    End Sub